package com.lcz.dao.impl;

import com.lcz.bean.Student;
import com.lcz.dao.DBUtils;
import com.lcz.dao.StudentDao;
import com.lcz.util.StudentEnum;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl extends DBUtils implements StudentDao {
    @Override
    public List<Student> getStudents(String stuname,String stuno,int sex,int pageIndex,int pageSize) {
        List list = new ArrayList<Student>();
        List params = new ArrayList();

        try {

            StringBuffer sqlbuffer = new StringBuffer("select * from student where 1=1 and state != 4");
            //为什么这里不定义一个新的ResultSet,把他作为全局变量？

            if (stuname!=null&&stuname.length()>0){
                sqlbuffer.append(" and stuname like ?");
                params.add("%" +stuname+"%");
            }
            if (stuno!=null&&stuno.length()>0){
                sqlbuffer.append(" and stuno like ?");
                params.add("%" +stuno+"%");
            }
            if (sex!=-1){
                sqlbuffer.append(" and sex like ?");
                params.add(sex);
            }
            //分页
            sqlbuffer.append(" limit ?,?");
            //limit (pageIndex-1)*pageSize, pageSize
            params.add((pageIndex-1)*pageSize);
            params.add(pageSize);

            resultSet = query(sqlbuffer.toString(), params);
            while (resultSet.next()){
                Student student = new Student();
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuno(resultSet.getString("stuno"));
                student.setStuname(resultSet.getString("stuname"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setProfession(resultSet.getString("profession"));
                student.setRegdate(resultSet.getDate("regdate"));
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }

    @Override
    public int total(String stuname, String stuno, int sex) {
        int total = 0;

        try {
            List params=new ArrayList();
            StringBuffer sqlbuffer = new StringBuffer("select count(*) from student where 1=1");
            //为什么这里不定义一个新的ResultSet,把他作为全局变量？
            if (stuname!=null&&stuname.length()>0){
                sqlbuffer.append(" and stuname like ?");
                params.add("%" +stuname+"%");
            }
            if (stuno!=null&&stuno.length()>0){
                sqlbuffer.append(" and stuno like ?");
                params.add("%" +stuno+"%");
            }
            if (sex!=-1){
                sqlbuffer.append(" and sex like ?");
                params.add(sex);
            }

            resultSet = query(sqlbuffer.toString(), params);
            if(resultSet.next()){
              total = resultSet.getInt(1);
                System.out.println("total++"+total);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }

    @Override
    public int insertStu(Student student) {
        int i =0;
        try {
            String sql="insert into student values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            List params=new ArrayList();
            params.add(student.getStuname());
            params.add(student.getStuno());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdnumber());
            params.add(student.getPolitics());
            params.add(new Date());
            params.add(StudentEnum.READING.type);
            params.add(student.getIntroduction());
            params.add(student.getGid());
            i = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return i;
    }

    @Override
    public Student findById(String stuid) {
        Student student = new Student();
        try {
            String sql="select * from student where stuid=? and state != 4";
            List params=new ArrayList();
            params.add(stuid);
            resultSet=query(sql,params);
            while(resultSet.next()){
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuno(resultSet.getString("stuno"));
                student.setStuname(resultSet.getString("stuname"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setProfession(resultSet.getString("profession"));
                student.setAddress(resultSet.getString("address"));
                student.setRegdate(resultSet.getDate("regdate"));
                student.setEmail(resultSet.getString("email"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));
                student.setRegistered(resultSet.getString("registered"));
                student.setIdnumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return student;
    }

    @Override
    public int update(Student student) {
        int update = 0;
        try {
            //这里需要大家自己完善非主键列以外的列的更新
            String sql="update student set stuname=?,address=?,sex=? where stuid=?";
            List params=new ArrayList();
            params.add(student.getStuname());
            params.add(student.getAddress());
            params.add(student.getSex());
            params.add(student.getStuid());

            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    @Override
    public int delete(String stuid) {
        int ans = 0;
        try {
            String sql = "update student set state = ? where stuid = ?";
            List params = new ArrayList();
            params.add(StudentEnum.DELETE.type);
            params.add(stuid);
            ans = update(sql,params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return ans;
    }

    @Override
    public List isStuname() {
        List list = null;
        try {
            list = new ArrayList();
            String sql = "select stuno from student";
            ResultSet resultSet = query(sql, null);
            while (resultSet.next()){
                list.add(resultSet.getString("stuno"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }
}
